import pymysql
import xlrd
wb = xlrd.open_workbook(filename=r"D:\FlowerRecognition\PythonProject\work\5.baidu-员工的人员信息.xls")
sheet = wb.sheet_by_index(0)

# Connect to the database
db = pymysql.connect(host='localhost',port=3306, user='root', password='20020802sixiuli', db='people')

# Create a cursor object
cursor = db.cursor()

# Execute the SQL query to create the table

i=1
while i<65534:
    row = sheet.row_values(i)
    sql = "insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    cursor.execute(sql,row)
i+=1
# Commit the changes to the database
db.commit()

# Close the database connection
cursor.close()
db.close()

num = 1
wb = xlrd.open_workbook(filename= r'D:\FlowerRecognition\PythonProject\work\5.baidu-员工的人员信息.xls')
sheet = wb.sheet_by_index(0)
print("总共{}个人".format(sheet.nrows-1))
i=0
boy=0
girl=0

col=sheet.col_values(8)
while i<65534:
    if col[i]=="男":
         boy+=1
    else :
         girl+=1
    i+=1
print("男生人数为：{}".format(boy))
print("女生人数为：{}".format(girl))
num=0
age=sheet.col_values(7)
j=1
while j<65534:
    if int(age[j])>45:
        num+=1
    j+=1
print("年龄超过45岁的老员工人数为：{}".format(num))
k=1
num1=0
num2=0
num3=0
money=sheet.col_values(11)
while k<65534:
    if money[k]>8000:
        num1+=1
    elif money[k]<3000:
        num2+=1
    else:
        num3+=1
    k+=1
print("薪资高于8000的人数是：{}".format(num1))
print("薪资低于3000的人数是：{}".format(num2))
media=["传媒"]
n=1
num4=0
company=sheet.col_values(13)
while n<65534:
    if media[0] in company[n]:
        num4+=1
    n+=1
print("传媒公司工作人员数量为：{}".format(num4))
num5=0
m=1
x=0
local=["黑龙江","北京","福建","四川"]
col=sheet.col_values(9)
while m<65534:
    if local[0] in col[m]:
            num5+=1
    elif local[1] in col[m]:
            num5+=1
    elif local[2] in col[m]:
            num5+=1
    elif local[3] in col[m]:
            num5+=1
m+=1
print("可能在疫情高危地区人数为：{}".format(num5))
tele11=["134","135","136","137","138","139","147","150","151","152","157","158","159","178","182","183","184","187","188"]
tele12=["1703","1705","1706"]
tele21=["130","131","132","145","155","156","175","176","185","186"]
tele22=["1704","1707","1708","1709","171"]
tele31=["133","149","153","173","177","180","181","189"]
tele32=["1700","1701","1702"]
num6=0
num7=0
num8=0
i=1
j=0
k=0
m=0
n=0
p=0
q=0
tele=sheet.col_values(5)
while i<65534:
    telem=tele[i][:3]
    telen=tele[i][:4]
    while j<19:
            if tele11[j] in telem:
               num6+=1
            else:
               while m<3:
                   if tele12[m] in telen:
                      num6+=1
               m+=1
    j+=1
    print("移动网络人数：{}".format(num6))
    while k<15:
        if tele21[k] in telem:
            num7+=1
        else:
            while n<5:
                if tele22[n] in telen:
                   num7+=1
            n+=1
    k+=1
    print("联通网络人数：{}".format(num7))
    while p<8:
        if tele21[p] in telem:
            num8+=1
        else:
            while q<3:
                if tele22[q] in telen:
                   num8+=1
            q+=1
    p+=1
    print("电信网络人数：{}".format(num8))

